<?php defined('SYSPATH') or die('No direct access allowed.');


class Model_Project_Comment extends ORM {

		// Relationships
	protected $_belongs_to = array('project' => array('model' => 'desktop_project','foreign_key' => 'project_id'),
                                       'user'    => array('model' => 'user', 'foreign_key' => 'user_id'));

        public function rules()
	{
		return array(
			'content' => array(
				array('not_empty')
			),
                        'parent_id' => array(
                                array('digit')
                        )
		);
	}
        
        public function filters()
	{
		return array(
			'content' => array(
                                //xss clean
				array(array('Security', 'xss_clean')),
                                array('trim')
			)              
		);
	}
        
        public function labels()
	{
		return array(
			'content'         => __('Comment')
		);
	}
        
        public function create(Validation $validation = NULL)
        {
            if ($this->_loaded)
                throw new Kohana_Exception('Cannot create :model model because it is already loaded.', array(':model' => $this->_object_name));

            // Require model validation before saving
            if ( ! $this->_valid)
            {
                $this->check($validation);
            }

            $data = array();
            foreach ($this->_changed as $column)
            {
                // Generate list of column => values
                $data[$column] = $this->_object[$column];
            }

            if (is_array($this->_created_column))
            {
                // Fill the created column
                $column = $this->_created_column['column'];
                $format = $this->_created_column['format'];

                $data[$column] = $this->_object[$column] = ($format === TRUE) ? time() : date($format);
            }

//            $result = DB::insert($this->_table_name)
//                ->columns()
//                ->values(array_values($data))
//                ->execute($this->_db);
//            
            $data_ = $data;
            unset($data_['parent_id']);
            
            $query =  'insert into `project_comments`('.implode(',',array_keys($data_)).', parent_id)
                       SELECT :'.implode(',:',array_keys($data_)).', (SELECT COALESCE(parent_id, id) FROM `project_comments` where id = :parent_id) as parent_id;';
            
            
            $db_query = DB::query(DATABASE::INSERT, $query);
            
            foreach($data_ as $key => $value)
            {
                $db_query->param(':'.$key, $value);
            }
            
            $result = $db_query->bind(':parent_id', $data['parent_id'])
                               ->execute($this->_db);
           
            if ( ! array_key_exists($this->_primary_key, $data))
            {
                // Load the insert id as the primary key if it was left out
                $this->_object[$this->_primary_key] = $this->_primary_key_value = $result[0];
            }

            if (empty($this->_primary_key_value))
            {
                // Set the primary key value if it was manually chosen by the user
                $this->_primary_key_value = $this->_object[$this->_primary_key];
            }

            // Object is now loaded and saved
            $this->_loaded = $this->_saved = TRUE;

            // All changes have been saved
            $this->_changed = array();

            return $this;
        }
        
        
        public function get_comment($project_id)
        {   
            $this->clear();
            $comment = $this->select('user_profiles.name', 
                                           'user_profiles.surname', 
                                           'user_profiles.description', 
                                           'user_profiles.url',
                                           array('user_profiles.file_id','photo'),
                                           array('files.id','file_id'),
                                           array('files.name','file_name'),
                                           array('files.extension','file_extension'))
                                  ->join('users')->on('project_comments.user_id', '=', 'users.id')
                                  ->join('user_profiles')->on('user_profiles.user_id', '=', 'users.id')
                                  ->join('files','LEFT')->on('files.id', '=', 'user_profiles.file_id')
                                  ->where('project_comments.id', '=', $project_id)
                                  ->find();

             $comment->photo = Media::get_file_path($comment->file_name, 
                                                    $comment->file_id, 
                                                    $comment->file_extension, 
                                                    true, 
                                                    'mini_square', 
                                                    false);
             return $comment;             
        }
        
        public function get_comments($project_id, $limit = false)
        {
             $temp_comments = DB::select('user_profiles.name', 
                                         'user_profiles.surname', 
                                         'user_profiles.description', 
                                         'user_profiles.url',
                                         array('user_profiles.file_id','photo'),
                                         array('files.id','file_id'),
                                         array('files.name','file_name'),
                                         array('files.extension','file_extension'),
                                         'project_comments.id',
                                         'project_comments.content',
                                         'project_comments.date_created',
                                         'project_comments.parent_id')
                              ->from('project_comments')
                              ->join('users')->on('project_comments.user_id', '=', 'users.id')
                              ->join('user_profiles')->on('user_profiles.user_id', '=', 'users.id')
                              ->join('files','LEFT')->on('files.id', '=', 'user_profiles.file_id')
                              ->where('project_comments.project_id', '=', $project_id)
                              ->order_by(DB::expr('coalesce(project_comments.parent_id, project_comments.id), project_comments.id'));
            
             if ($limit)
                $temp_comments->limit($limit);
                  
             $temp_comments = $temp_comments->as_object()->execute();
             
             $comments = array();
             foreach($temp_comments as $comment)
             { 
                 
                 $comment->photo = Media::get_file_path($comment->file_name, 
                                                        $comment->file_id, 
                                                        $comment->file_extension, 
                                                        true, 
                                                        'mini_square', 
                                                        false);
                 
                 $comments[] = $comment;
             }
              return $comments;
        }
        
        public function get_amount($project_id)
        {
            $amount =  DB::select(array('COUNT("*")', 'total_comments'))
                         ->from('project_comments')
                         ->where('project_id', '=', $project_id)
                         ->execute()
                         ->get('total_comments', 0);

            return $amount;
        }
        
}